﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
namespace BLL
{
    public class DBhelp
    {
        string path = string.Format("Data Source={0}", System.Environment.CurrentDirectory + "\\db\\db.db");
        /// <summary>
        /// 执行数据库增删改的方法（带参）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="par">参数</param>
        /// <returns></returns>
        public Boolean add(string sql, SQLiteParameter[] par)
        {
            using (SQLiteConnection conn = new SQLiteConnection(path))
            {

                conn.Open();
                SQLiteCommand comm = new SQLiteCommand(conn);
                comm.CommandText = sql;
                comm.Parameters.AddRange(par);
                if (comm.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

        }
        /// <summary>
        /// 这个是执行数据库增删改的方法（不带参）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public Boolean add(string sql)
        {
            
            using (SQLiteConnection conn = new SQLiteConnection(path))
            {
                conn.Open();
                SQLiteCommand comm = new SQLiteCommand(conn);
                comm.CommandText = sql;
                if (comm.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

        }
        /// <summary>
        /// 执行数据库查询的方法（带参）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="par">参数</param>
        /// <returns></returns>
        public DataTable select(string sql, SQLiteParameter[] par)
        {
            using (SQLiteConnection conn = new SQLiteConnection(path))
            {
                conn.Open();
                SQLiteCommand comm = new SQLiteCommand(conn);
                DataSet dt = new DataSet();
                comm.CommandText = sql;
                comm.Parameters.AddRange(par);
                SQLiteDataAdapter adpter = new SQLiteDataAdapter(comm);
                adpter.Fill(dt);
                return dt.Tables[0];
            }
        }
        /// <summary>
        /// 这个是执行数据库查询的方法（不带参）
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public DataTable select(string sql)
        {
            using (SQLiteConnection conn = new SQLiteConnection(path))
            {
                conn.Open();
                SQLiteCommand comm = new SQLiteCommand(conn);
                DataSet dt = new DataSet();
                comm.CommandText = sql;
                SQLiteDataAdapter adpter = new SQLiteDataAdapter(comm);
                adpter.Fill(dt);
                return dt.Tables[0];
            }
        }
    }
    
}
